<?php

namespace app\result;

use app\model\extend\PCashbook;
use app\model\extend\PCashbookUser;
use app\model\user\AmountType;
use app\model\user\Cashflows;
use app\model\user\CashflowsCycle;
use app\model\user\Categories;
use app\model\user\User;
use app\model\user\UserBudget;
use app\result\extend\CashbookResult;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Exception;
use think\facade\App;
use think\facade\Db;

class CashflowResult extends BaseResult
{
    /**
     * @param $data
     * @param $id
     * @return mixed
     */
    private static function getDataInfo($data, $id)
    {
        if (!empty($data['budget_id'])) {
            $data['budget_title'] = (new UserBudget())->get_value(['budget_id' => $data['budget_id']], 'title');
        }
        if (!empty($data['cashbook_id'])) {
            $data['cashbook_title'] = (new PCashbook())->get_value(['cashbook_id' => $data['cashbook_id']], 'name');
        }
        $data['cycle'] = (new CashflowsCycle())->data_get(['cashflows_id' => $id]);
        if ($data['cycle']) {

            //编辑需要
            $data['is_cycle'] = true;
            $data['cycle_type'] = $data['cycle']['cycle_type'];
            $data['cycle_days'] = $data['cycle']['cycle_days'];

            //daily每日，weekly每周，monthly每月，quarterly每季度，yearly每年，custom自定义天数
            $data['cycle']['cycle_type'] = self::getCycleType($data['cycle_type'], $data['cycle_days']);
            $data['cycle']['next_time'] = date('Y-m-d', strtotime($data['cycle']['next_time']));
        }
        return $data;
    }

    private static function getCycleType($cycle_type, $cycle_days): string
    {
        $cycle_type = match ($cycle_type) {
            'daily' => '每日',
            'weekly' => '每周',
            'monthly' => '每月',
            'quarterly' => '每季度',
            'yearly' => '每年',
            'custom' => '每' . $cycle_days . '天',
            default => '未知'
        };
        return $cycle_type;
    }

    public function ready($user_id, $args = [])
    {
        $id = intval($args['id'] ?? 0);
        $type = $args['type'] == 0 ? 20 : 10;//0=支出，1=收入
        $args['type'] = $type;

        if ($id) {
            $data = (new Cashflows())->data_get(['id' => $id, 'user_id' => $user_id]);
            if ($data) {
                $data = $data->toArray();
                $data = self::getDataInfo($data, $id);
            }
        }
        $where = [
            'user_id' => $user_id,
        ];
        if ($type) {
            $where['type'] = $type;
        }
        //分类
        $category = (new Categories())->data_list_nopage($where, 'id,name,type,icon', 'sort desc,id DESC');
        //收支类型
        $amountType = (new AmountType())->data_list_nopage($where, 'id,name,type', 'sort desc,id DESC');
        if ($amountType) {
            $accountTypes = array_column($amountType, 'name');
        } else {
            $accountTypes = ['微信', '支付宝', '银行卡', '现金', '信用卡', '其他'];
        }

        foreach ($accountTypes as &$v) {
            $v = ['label' => $v, 'value' => $v];
        }
        $default_amount_type = (new Cashflows())->get_value(['user_id' => $user_id], 'amount_type', 'date desc,id desc');

        //预算
        $budget = (new BudgetResult())->list_option($user_id, $args, true);

        //自定义配置
        $diy_action = UserSettingResult::get($user_id, 'diy_action_config');
        if ($diy_action) {
            $diy_action = array_column($diy_action, null, 'key');
        }
        if (!empty($diy_action['enable_cashbook']['value'])) {
            $default_cashbook = (new CashbookResult())->get_default_cashbook($user_id);
        }

        $cashbook_list = (new CashbookResult())->list_option($user_id, $args, true);

        return self::success_return('success', [
            'category' => $category,
            'info' => $data ?? null,
            'accountTypes' => $accountTypes,
            'default_amount_type' => $default_amount_type,
            'budget_list' => $budget,
            'diy_action' => $diy_action,
            'cashbook_list' => $cashbook_list,
            'default_cashbook' => $default_cashbook ?? []
        ]);
    }

    //新增记录
    public function action($user_id, $args)
    {
        if (empty($args['category_id'])) {
            return self::error_return('请选择分类');
        }
        if (empty($args['type']) || !in_array($args['type'], [10, 20])) {
            return self::error_return('请选择收支类型');
        }
        if (empty($args['amount']) || !is_numeric($args['amount']) || $args['amount'] <= 0) {
            return self::error_return('请输入金额');
        }
        if ($args['amount'] > 100000000) {
            return self::error_return('土豪，金额过大，不能超过1亿！');
        }
        if (empty($args['date']) || !preg_match('/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/', $args['date'])) {
            return self::error_return('请选择日期');
        }
        $id = $args['id'] ?? 0;
        if ($id) {
            $model = new Cashflows();
            $data = $model->data_get(['id' => $id, 'user_id' => $user_id]);
            if (empty($data['id'])) {
                return self::error_return('记录不存在');
            }
        }
        if (!empty($args['budget_id'])) {
            $budget = (new UserBudget())->data_get(['budget_id' => $args['budget_id'], 'user_id' => $user_id, 'del_time' => 0]);
            if (empty($budget['budget_id'])) {
                return self::error_return('预算不存在');
            }
            if ($args['type'] == 10 && !in_array($budget['type'], [10, 0])) {
                return self::error_return('预算类型不匹配');
            } elseif ($args['type'] == 20 && !in_array($budget['type'], [20, 0])) {
                return self::error_return('预算类型不匹配');
            }
        }
        $category = (new Categories())->data_get(['id' => $args['category_id'], 'user_id' => $user_id]);
        if (empty($category['id'])) {
            return self::error_return('分类不存在');
        }
        if ($category['type'] != $args['type']) {
            return self::error_return('请选择' . ($args['type'] == 20 ? '支出' : '收入') . '分类');
        }
        $is_cycle = !empty($args['is_cycle']);//是否周期
        //循环周期：daily每日，weekly每周，monthly每月，quarterly每季度，yearly每年，custom自定义天数
        $cycle_type = $args['cycle_type'] ?? '';
        if (($is_cycle && $cycle_type == 'custom') && (empty($args['cycle_days']) || $args['cycle_days'] < 1)) {
            return self::error_return('请填写自定义循环天数，不能小于1');
        }
        $baseData = [
            'type' => $args['type'],
            'category_id' => $args['category_id'],
            'cashbook_id' => $args['cashbook_id'] ?? 0,
            'amount' => $args['amount'],
            'budget_id' => $args['budget_id'] ?? 0,
            'date' => $args['date'],
            'remark' => $args['remark'] ?? '',
            'amount_type' => trim($args['amount_type'] ?? ''),
            'image' => $args['image'] ?? '',
            'user_id' => $user_id,
            'year' => date('Y', strtotime($args['date'])),
            'month' => date('m', strtotime($args['date'])),
            'day' => date('d', strtotime($args['date'])),
        ];
        if ($id) {
            $res = (new Cashflows())->data_update(['id' => $id], $baseData);
        } else {
            $ckey = 'cashflow_action_' . $user_id . '_' . md5(json_encode($args));
            if (!LockResult::logck_by_second($ckey, 3)) {
                return self::error_return('正在处理，请勿频繁提交');
            }
            $id = (new Cashflows())->data_add($baseData);
            $res = $id;
            if ($id) {
                bs_queue(self::class, 'sync_count', ['user_id' => $user_id], time(), 2);
                // (new User())->where('user_id', '=', $user_id)->inc('all_bill_count', 1)->save();
            }
        }
        if ($is_cycle) {
            $getDays = function ($cycle_type, $cycle_days) {
                return match ($cycle_type) {
                    'daily' => 1,
                    'weekly' => 7,
                    'monthly' => 30,
                    'quarterly' => 90,
                    'yearly' => 365,
                    default => $cycle_days,
                };
            };
            $cycle_days = $getDays($cycle_type, $args['cycle_days'] ?? 0);
            $next_time = self::getNextTime($cycle_type, $args['date'], $cycle_days);
            $cycle_data = [
                'cashflows_id' => $id,
                'cycle_days' => $cycle_days,
                'cycle_type' => $cycle_type,
                'next_time' => $next_time,
            ];
            $hasCycle = (new CashflowsCycle())->data_get(['cashflows_id' => $id]);
            if ($hasCycle) {
                $res = (new CashflowsCycle())->data_update(['cashflows_id' => $id], $cycle_data);
            } else {
                $res = (new CashflowsCycle())->data_add($cycle_data);
                if (!$res) {
                    return self::error_return('保存周期失败');
                }
            }
        }


        if (!empty($baseData['budget_id'])) {
            bs_queue(BudgetResult::class, 'update_amount', [
                'user_id' => $user_id,
                'budget_id' => $baseData['budget_id']
            ], time(), 2);
        }

        return $res ? self::success_return('保存成功') : self::error_return($id ? '保存失败' : '数据未修改');
    }

    //获取下次循环时间
    public static function getNextTime($cycle_type, $date, $cycle_days = 0)
    {
        if ($cycle_type == 'custom' && $cycle_days) {
            return date('Y-m-d H:i:s', strtotime('+' . $cycle_days . ' days', strtotime($date)));
        }
        return match ($cycle_type) {
            'daily' => date('Y-m-d H:i:s', strtotime('+1 day', strtotime($date))),
            'weekly' => date('Y-m-d H:i:s', strtotime('+1 week', strtotime($date))),
            'monthly' => date('Y-m-d H:i:s', strtotime('+1 month', strtotime($date))),
            'quarterly' => date('Y-m-d H:i:s', strtotime(' +3 month', strtotime($date))),
            'yearly' => date('Y-m-d H:i:s', strtotime('+1 year', strtotime($date))),
            default => '',
        };
    }

    //同步账单数量,队列任务
    public function sync_count($args = [])
    {
        $user_id = $args['user_id'] ?? 0;
        if (empty($user_id)) {
            return self::error_return('参数错误');
        }
        $allCount = (new Cashflows())->where('user_id', '=', $user_id)->count();
        return (new User())->where('user_id', '=', $user_id)->update(['all_bill_count' => $allCount]);
    }

    public function get($user_id, $id)
    {
        $model = new Cashflows();
        $model->with = ['category'];
        $data = $model->data_get(['id' => $id]);
        if (empty($data['id'])) {
            return self::error_return('账单未找到');
        }
        $data['is_owner'] = 1;
        if ($data['user_id'] != $user_id && empty($data['cashbook_id'])) {
            return self::error_return('账单未找到');
        } elseif ($data['user_id'] != $user_id && !empty($data['cashbook_id'])) {
            //共享账本
            $hasJoined = (new PCashbookUser())
                ->where([['user_id', '=', $user_id], ['cashbook_id', '=', $data['cashbook_id']]])
                ->value('id');
            if (empty($hasJoined)) {
                return self::error_return('账单不匹配，请查看自己的账单');
            } else {
                $data['is_owner'] = 0;//不支持编辑其他人的账单
            }
        }
        $data = $data->toArray();
        $data['username'] = $data['is_owner'] ? '本尊' : (new User())->get_value(['user_id' => $data['user_id']], 'username');
        $data = self::getDataInfo($data, $id);
        return self::success_return('success', $data);
    }

    //删除
    public function delete($user_id, $id)
    {
        $model = new Cashflows();
        $data = $model->data_get(['id' => $id, 'user_id' => $user_id]);
        if (empty($data['id'])) {
            return self::error_return('记录不存在');
        }
        $res = $model->data_del(['id' => $id, 'user_id' => $user_id]);
        if (!$res) {
            self::error_return('删除失败');
        }
        if (!empty($data['budget_id'])) {
            bs_queue(BudgetResult::class, 'update_amount', [
                'user_id' => $user_id,
                'budget_id' => $data['budget_id']
            ], time(), 1);
        }
        (new CashflowsCycle())->data_del(['cashflows_id' => $id]);//删除周期
        bs_queue(CashflowResult::class, 'sync_count', [
            'user_id' => $user_id
        ], time(), 1);
        return self::success_return('删除成功');
    }

    public function list_all_search_params($user_id, $args = [])
    {
        $from = $args['from'] ?? '';//statistics
        $budget = (new BudgetResult())->list_option($user_id, ['is_all' => 1], true);
        $amountType = (new Cashflows())->data_list_nopage(['user_id' => $user_id], 'amount_type', '', 'amount_type');
        if ($amountType) {
            $accountTypes = array_unique(array_column($amountType, 'amount_type'));
        } else {
            $accountTypes = ['微信', '支付宝', '银行卡', '现金', '信用卡', '其他'];
        }

        foreach ($accountTypes as &$v) {
            if ($v == '') {
                $v = '未分类';
            }
            $v = ['label' => $v, 'value' => $v];
        }

        $cashbook = (new CashbookResult())->list_option($user_id, ['is_all' => 1], true);
        if ($from <> 'statistics') {
            $allCategory = (new CategoryResult())->list_option($user_id);
        }


        $timeType = [];
        $timeType[] = ['label' => '全部', 'value' => ''];
        $timeType[] = ['label' => '按日历范围', 'value' => 'calendar'];
        $timeType[] = ['label' => '按年份筛选', 'value' => 'year'];
        $timeType[] = ['label' => '按月份筛选', 'value' => 'month'];
        $sortList = [];
        $sortList[] = ['label' => '默认', 'value' => ''];
        $sortList[] = ['label' => '金额降序', 'value' => 'amount_desc'];
        $sortList[] = ['label' => '金额升序', 'value' => 'amount_asc'];
        $sortList[] = ['label' => '日期降序', 'value' => 'date_desc'];
        $sortList[] = ['label' => '日期升序', 'value' => 'date_asc'];

        $type = [];
        $type[] = ['label' => '全部', 'value' => ''];
        $type[] = ['label' => '支出', 'value' => 20];
        $type[] = ['label' => '收入', 'value' => 10];

        return self::success_return('success', [
            'budget_list' => $budget ?? [],
            'cashbook_list' => $cashbook ?? [],
            'amount_type' => array_values($accountTypes) ?? [],
            'type' => $type,
            'time_type' => $timeType,
            'sort_list' => $sortList,
            'category_list' => $allCategory ?? []
        ]);
    }

    public function list_all($user_id, $args = [])
    {
        $page = intval($args['page'] ?? 1);
        $limit = intval($args['limit'] ?? 10);
        $keywords = trim($args['keywords'] ?? '');
        $type = intval($args['type'] ?? 0);
        $amount_type = trim($args['amount_type'] ?? '');
        $time_type = trim($args['time_type'] ?? '');
        $year = intval($args['year'] ?? 0);
        $month = intval($args['month'] ?? 0);
        $budget_id = intval($args['budget_id'] ?? 0);
        $category_id = intval($args['category_id'] ?? 0);
        $cashbook_id = intval($args['cashbook_id'] ?? 0);
        $sort = trim($args['sort'] ?? 'date desc');
        $where['a.user_id'] = ['a.user_id', '=', $user_id];
        if ($type) {
            $where[] = ['a.type', '=', $type];
        }
        if ($budget_id) {
            $where[] = ['a.budget_id', '=', $budget_id];
        }
        if ($category_id) {
            $where[] = ['a.category_id', '=', $category_id];
        }
        if ($cashbook_id) {
            //查询所有关联用户
            $user_ids = (new PCashbookUser())->data_list_nopage(['cashbook_id' => $cashbook_id], 'user_id');
            $user_ids = array_column($user_ids, 'user_id');
            if (!in_array($user_id, $user_ids)) {
                //非法查询
                $cashbook_id = -1;//让其找不到数据
            }
            unset($where['a.user_id']);//避免不必要的麻烦
            $where[] = ['a.user_id', 'in', $user_ids];
            $where[] = ['a.cashbook_id', '=', $cashbook_id];
        }
        if ($amount_type) {
            $where[] = ['a.amount_type', '=', $amount_type === '未分类' ? '' : $amount_type];
        }
        if ($year) {
            $where[] = ['a.year', '=', $year];
        }
        if ($month) {
            $where[] = ['a.month', '=', $month];
        }
        if ($keywords) {
            $where[] = ['a.remark|a.amount_type|b.name', 'like', '%' . $keywords . '%'];
        }
        if ($time_type === 'range') {
            if (!empty($args['start_time'])) {
                $where[] = ['a.date', '>=', $args['start_time']];
            }
            if (!empty($args['end_time'])) {
                $where[] = ['a.date', '<=', $args['end_time']];
            }
        }
        $order = 'a.date desc,a.id desc';
        $sortArr = [
            'date_desc' => 'a.date desc,a.id desc',
            'date_asc' => 'a.date asc,a.id desc',
            'amount_desc' => 'a.amount desc,a.id desc',
            'amount_asc' => 'a.amount asc,a.id desc',
        ];
        $order = $sortArr[$sort] ?? $order;
        $model = new Cashflows();

        $where = array_values($where);
        $total = $model->alias('a')
            ->join('categories b', 'a.category_id=b.id', 'left')
            ->where($where)
            ->count();

        $data = $model->alias('a')
            ->join('categories b', 'a.category_id=b.id', 'left')
            ->field('a.*,b.name as category_name,b.icon as category_icon')
            ->where($where)
            ->order($order)
            ->page($page, $limit)
            ->select();

        if ($data) {
            $showSet = (new User())->data_get(['user_id' => $user_id], 'show_in_amount,show_out_amount');
            $totalIncome = !$showSet['show_in_amount'] ? '***' : $model
                ->alias('a')
                ->join('categories b', 'a.category_id=b.id', 'left')
                ->where(array_merge($where, ['a.type' => 10]))
                ->sum('a.amount');
            $totalOutcome = !$showSet['show_out_amount'] ? '***' : $model
                ->alias('a')
                ->join('categories b', 'a.category_id=b.id', 'left')
                ->where(array_merge($where, ['a.type' => 20]))
                ->sum('a.amount');
            $diff_amount = $showSet['show_in_amount'] && $showSet['show_out_amount'] ? bcsub($totalIncome, $totalOutcome, 2) : '***';
            $modelCycle = new CashflowsCycle();
            $modelCashbook = new PCashbook();
            $modelBudget = new UserBudget();
            $modelUser = new User();
            $data = $data->toArray();
            foreach ($data as &$v) {
                $cycle = $modelCycle->data_get(['cashflows_id' => $v['id']], 'cycle_type,cycle_days');
                $v['is_cycle'] = 0;
                if ($cycle) {
                    $v['is_cycle'] = 1;
                    $v['cycle_days'] = self::getCycleType($cycle['cycle_type'], $cycle['cycle_days']);
                    $v['cycle_type'] = '周期';
                }
                if (!empty($v['cashbook_id'])) {
                    $v['cashbook_title'] = $modelCashbook->get_value(['cashbook_id' => $v['cashbook_id']], 'name');
                }
                if (!empty($v['budget_id'])) {
                    $v['budget_title'] = $modelBudget->get_value(['budget_id' => $v['budget_id']], 'title');
                }
                if (!empty($v['image'])) {
                    $v['image'] = explode(',', $v['image']);
                }
                $v['is_owner'] = $v['user_id'] == $user_id;
                $v['username'] = $v['is_owner'] ? '本尊' : $modelUser->get_value(['user_id' => $v['user_id']], 'username');
                $v['category'] = [
                    'name' => $v['category_name'],
                    'icon' => $v['category_icon']
                ];
                $v['amount'] = $v['type'] == 10 ? ($showSet['show_in_amount'] ? number_format($v['amount'], 2, '.', '') : '***') : ($showSet['show_out_amount'] ? number_format($v['amount'], 2, '.', '') : '***');
            }
        }
        return self::success_return('success', [
            'list' => $data,
            'total' => $total,
            'total_income' => $totalIncome ?? 0,
            'total_outcome' => $totalOutcome ?? 0,
            'diff_amount' => $diff_amount ?? 0,
            'page' => $page,
            'limit' => $limit
        ]);

    }

    public function list($user_id, $args = [])
    {
        $cashbook_id = intval($args['cashbook_id'] ?? 0);
        $year = intval($args['year'] ?? 0);
        $month = intval($args['month'] ?? 0);
        $type = intval($args['type'] ?? 0);//10=收入，20=支出
        $where = [
            'user_id' => $user_id,
        ];
        if ($cashbook_id) {
            // $where['cashbook_id'] = $cashbook_id;
        }
        if ($year) {
            $where['year'] = $year;
        }
        if (!$month) {
            $month = date('m');
        }
        $where['month'] = $month;
        if ($type) {
            $where['type'] = $type;
        }
        $model = new Cashflows();
        $income = $model->data_sum(array_merge($where, ['type' => 10]), 'amount');
        $outcome = $model->data_sum(array_merge($where, ['type' => 20]), 'amount');
        $data = $model->data_list_nopage($where, '*', 'date desc', 'date');


        $list = [];
        $showSet = (new User())->data_get(['user_id' => $user_id], 'show_in_amount,show_out_amount');
        [$showIn, $showOut] = [$showSet['show_in_amount'], $showSet['show_out_amount']];
        $days = array_column($data, 'date');
        foreach ($days as $date) {
            $model->with = ['category'];
            $subList = $model->data_list_nopage(['user_id' => $user_id, 'date' => $date], '*', 'date desc, id desc');
            $in = $out = 0;
            if ($subList) {
                foreach ($subList as &$v) {
                    if ($v['type'] == 10) {
                        $in += $v['amount'];
                        $v['amount'] = $showIn ? number_format($v['amount'], 2, '.', '') : '***';
                    } else {
                        $out += $v['amount'];
                        $v['amount'] = $showOut ? number_format($v['amount'], 2, '.', '') : '***';
                    }
                    if ($v['amount_type']) {
                        $v['remark'] = '(' . $v['amount_type'] . ')' . $v['remark'];
                        // $v['category']['name'] = $v['category']['name'].'('.$v['amount_type'].')';
                    }

                }
                unset($v);
            }
            $list[] = [
                'in' => $showIn ? bcmul($in, 1, 2) : '***',
                'out' => $showOut ? bcmul($out, 1, 2) : '***',
                'list' => $subList,
                'time' => date('m月d日', strtotime($date)),
                'week' => $this->getWeek($date),
            ];
        }

        return self::success_return('success', [
            'in' => $showIn ? number_format($income, 2, '.', '') : '****',
            'out' => $showOut ? number_format($outcome, 2, '.', '') : '****',
            'show_set' => [
                'in' => $showIn,
                'out' => $showOut
            ],
            'list' => $list
        ]);
    }

    //获取星期
    private function getWeek($date)
    {
        $week = date('w', strtotime($date));
        switch ($week) {
            case 0:
                return '周日';
            case 1:
                return '周一';
            case 2:
                return '周二';
            case 3:
                return '周三';
            case 4:
                return '周四';
            case 5:
                return '周五';
            case 6:
                return '周六';
        }
        return '';
    }

    public function set_display($user_id, $args)
    {
        $type = trim($args['type'] ?? '');
        if (!$type) {
            return self::error_return('参数错误');
        }
        $showSet = (new User())->data_get(['user_id' => $user_id], 'show_in_amount,show_out_amount');
        if ($type == 'in') {
            $data = ['show_in_amount' => $showSet['show_in_amount'] == 1 ? 0 : 1];
        } else {
            $data = ['show_out_amount' => $showSet['show_out_amount'] == 1 ? 0 : 1];
        }

        (new User())->data_update(['user_id' => $user_id], $data);
        return self::success_return('设置成功');
    }

    //导出账单
    public function export($user_id, $args = [])
    {
        if (empty($args['encryptedData']) || empty($args['iv'])) {
            return $this->error_return('参数错误');
        }
        $res = ToolsResult::decrypt($args['encryptedData'], config('tools.encode.key'), $args['iv']);
        if (!$res) {
            return $this->error_return('参数非法');
        }
        $args = json_decode($res, true);
        $email = trim($args['email'] ?? '');
        if (!$email) {
            return self::error_return('请输入邮箱');
        } elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            return self::error_return('邮箱格式不正确');
        }
        $pwd = trim($args['password'] ?? '');
        if (!$pwd) {
            return self::error_return('请输入密码');
        }
        $myInfo = (new User())->data_get(['user_id' => $user_id], 'password,salt');
        $pwd = md5($pwd . $myInfo['salt']);
        if ($pwd != $myInfo['password']) {
            return self::error_return('密码错误');
        }


        $where = [['a.user_id', '=', $user_id]];
        $model = new Cashflows();
        $data = $model->alias('a')
            ->join('categories b', 'a.category_id=b.id', 'left')
            ->field('a.*,b.name as category_name,b.icon as category_icon')
            ->where($where)
            ->order('a.date desc,a.id desc')
            ->select();
        if (!$data) {
            return self::error_return('没有数据');
        }
        //导出excel
        $spreadsheet = new Spreadsheet();
        $spreadsheet->getActiveSheet()->setCellValue('A1', '类型');
        $spreadsheet->getActiveSheet()->setCellValue('B1', '分类');
        $spreadsheet->getActiveSheet()->setCellValue('C1', '收支方式');
        $spreadsheet->getActiveSheet()->setCellValue('D1', '金额');
        $spreadsheet->getActiveSheet()->setCellValue('E1', '年');
        $spreadsheet->getActiveSheet()->setCellValue('F1', '月');
        $spreadsheet->getActiveSheet()->setCellValue('G1', '日');
        $spreadsheet->getActiveSheet()->setCellValue('H1', '日期');
        $spreadsheet->getActiveSheet()->setCellValue('I1', '备注');
        $spreadsheet->getActiveSheet()->setCellValue('J1', '附件图片');

        $i = 2;
        foreach ($data as $v) {
            $spreadsheet->getActiveSheet()->setCellValue('A' . $i, $v['type'] == 10 ? '收入' : '支出');
            $spreadsheet->getActiveSheet()->setCellValue('B' . $i, $v['category_name']);
            $spreadsheet->getActiveSheet()->setCellValue('C' . $i, $v['amount_type']);
            $spreadsheet->getActiveSheet()->setCellValue('D' . $i, $v['amount']);
            $spreadsheet->getActiveSheet()->setCellValue('E' . $i, $v['year']);
            $spreadsheet->getActiveSheet()->setCellValue('F' . $i, $v['month']);
            $spreadsheet->getActiveSheet()->setCellValue('G' . $i, $v['day']);
            $spreadsheet->getActiveSheet()->setCellValue('H' . $i, $v['date']);
            $spreadsheet->getActiveSheet()->setCellValue('I' . $i, $v['remark']);
            $spreadsheet->getActiveSheet()->setCellValue('J' . $i, $v['image']);
            $i++;
        }
        //标题加粗居中
        $spreadsheet->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);
        $spreadsheet->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
        //自动换行
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        //全部垂直居中
        $spreadsheet->getActiveSheet()->getStyle('A1:J' . ($i - 1))->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);


        $path = App::getRuntimePath() . '/temp/excel/';
        if (!is_dir($path)) {
            @mkdir($path, 0777, true);
        }
        $this->clean_file($path);//清理一下旧文件

        $fileName = md5($user_id) . '.xlsx';

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        try {
            $writer->save($path . $fileName);
        } catch (Exception $e) {
            return self::error_return('导出失败');
        }

        $localPath = $path . $fileName;//本地文件路径
        //生成zip压缩包
        $zip = new \ZipArchive();
        $password = rand(100000, 999999);//随机生成一个密码
        $zipFile = $path . '账单文件-' . md5($user_id) . '.zip';
        if (file_exists($zipFile)) {
            @unlink($zipFile);
        }
        $readme = $path . 'readme.txt';
        $readmeContent = '请将zip包解压，解压密码请查看记账APP的系统消息，解压后打开文件，即可看到账单信息';
        file_put_contents($readme, $readmeContent);
        if ($zip->open($zipFile, \ZipArchive::CREATE) === true) {
            $zip->setPassword($password);
            $zip->addFile($localPath, "账单.xlsx");
            $zip->addFile($readme, '解压密码.txt');
            $zip->setEncryptionName('账单.xlsx', \ZipArchive::EM_AES_256, $password);//按照文件名加密
            // $zip->setEncryptionIndex(0, \ZipArchive::EM_AES_256,$password);//第一个文件加密
            $zip->close();
            @unlink($localPath);
            @unlink($readme);
        }
        //发送邮件
        $res = (new EmailResult())->send_email([
            'title' => '账单导出',
            'sub_title' => '账单数据',
            'content' => "<html><head lang='zh'><meta charset=\"UTF-8\"><title>账单导出</title></head>
    <body>
    <p>亲爱的用户：您好！</p>
    <p>你的账单记录明细已发送到您邮箱，请下载附件查阅。</p>
    <p>出于安全考虑，附件已加密，解压密码已通过记账软件系统消息发送至收件箱中，请留意相关通知。</p>
    <p>建议从电脑端下载查阅。如无法解压，请尝试更换解压软件。</p>
    </body>
    
    </html>",
            'user_id' => $args['user_id'] ?? 0,
            'email' => $args['email'],
            'attachment' => $zipFile,
        ]);
        if ($res['code'] != 0) {
            return $res;
        }
        UserMsgResult::add($user_id, [
            'title' => '账单导出成功',
            'content' => '您的账单已导出到邮箱' . $email . '，请注意查收，出于安全考虑，附件已加密，解压请输入密码：' . $password,
            'type' => 1,
        ]);

        unlink($zipFile);
        return self::success_return('账单已发送到' . $email . '，请查收');
    }

    private function clean_file($path)
    {
        //清理掉旧的文件，7天以前
        $fileList = scandir($path);
        $time = time();
        foreach ($fileList as $file) {
            if (in_array($file, ['.', '..'])) {
                continue;
            }
            $filePath = $path . $file;
            if (is_file($filePath) && $time - filemtime($filePath) > 86400 * 7) {
                @unlink($filePath);
            }
        }
    }

    public function import($user_id, $args = [])
    {
        if (empty($args['encryptedData']) || empty($args['iv'])) {
            return $this->error_return('参数错误');
        }
        $res = ToolsResult::decrypt($args['encryptedData'], config('tools.encode.key'), $args['iv']);
        if (!$res) {
            return $this->error_return('参数非法');
        }
        $args = json_decode($res, true);

        $file_key = $args['file_key'] ?? '';
        if (!$file_key) {
            return self::error_return('参数错误');
        }
        $data = cache($file_key);
        if (!$data) {
            return self::error_return('文件已过期，请重新识别');
        }
        $password = $args['password'] ?? '';
        if (empty($password)) {
            return $this->error_return('密码不能为空');
        }
        $myInfo = (new User())->data_get(['user_id' => $user_id], 'account,account_edit_time,salt,password');

        if (md5($password . $myInfo['salt']) <> $myInfo['password']) {
            return $this->error_return('密码错误');
        }
        $modelCategory = new Categories();
        foreach ($data as &$v) {
            $category_id = $modelCategory->get_value(
                [['user_id', '=', $user_id], ['name', '=', $v['category_name']]], 'id');
            if (!$category_id) {
                $category_id = (new Categories())->data_add([
                    'user_id' => $user_id,
                    'name' => $v['category_name'],
                    'type' => $v['type'],
                    'icon' => 'red-packet'
                ]);
            }
            $v['category_id'] = $category_id;
        }
        unset($v);

        //data_return('dsts', 500, $data);
        $res = (new Cashflows())->saveAll($data, false);
        if (!$res) {
            return self::error_return('导入失败');
        }
        cache($file_key, null);
        bs_queue(self::class, 'sync_count', ['user_id' => $user_id], time(), 2);
        return self::success_return('导入成功，导入数据：' . count($data) . '条');
    }

    //导入模板
    public function import_tpl()
    {
        $readme = [];
        $readme['alipay'] = [
            '打开支付宝APP',
            '进入【我的】',
            '点击【账单】',
            '选择【右上角三个点"..."】',
            '选择【开具交易流水证明】',
            '选择【用于个人对账】',
            '选择【下一步】',
            '输入接收邮箱',
            '选择【发送】',
            '等待邮件发送完成',
            '解压文件',
            '返回本页面，选择【选择文件】，选择发送的附件，点击【确定导入】即可。',
            '【注意】：程序会自动过滤“【不计收支】”的类型，会自动创建分类（建议先自行修改csv里面的分类名称），并自动过滤掉无效的账单。'
        ];
        $readme['wechat'] = [
            '打开微信APP',
            '进入【我的】',
            '点击【服务】',
            '选择【钱包】',
            '选择【账单】',
            '选择【常见问题】',
            '选择【下载账单】',
            '选择【用于个人对账】',
            '选择【交易类型】',
            '选择【账单时间】（微信这里最多只能三个月）',
            '选择【下一步】',
            '输入接收邮箱',
            '选择【下一步】',
            '输入密码',
            '等待邮件发送完成，解压文件（看微信的消息会有解压密码哦）然后返回本页面，选择【选择文件】，选择发送的附件，点击【确定导入】即可。',
            '【注意】：微信的账单格式不规范，没有分类，会默认创建【微信导入】分类，并自动过滤掉无效的账单。'
        ];
        $readme['bs_custom'] = [
            '请先下载模板文件，然后按照模板格式填写账单信息。',
            '打开excel文件，将账单信息按模板格式填写。',
            '点击【选择文件】，选择您填写好的excel文件，点击【确定导入】即可。'
        ];

        return self::success_return('success', [
            'url' => request()->domain() . '/storage/tpl/bill_import_tpl.xlsx',
            'readme' => $readme,
        ]);
    }

    private function read_alipay_csv($filePath, $user_id, $fileKey)
    {
        $data = [];
        // 打开文件
        $file = fopen($filePath, 'r');

        if ($file === false) {
            return self::error_return('文件打开失败');
        }

        while (($row = fgetcsv($file)) !== false) {
            // 将每一行数据从 GBK 转换为 UTF-8 编码
            $row = array_map(function ($from, $to, $value) {
                // 检查$value是否为null，如果是，则直接返回null，不进行转换
                if ($value === null) {
                    return null;
                }
                // 进行字符集转换
                return iconv($from, $to, $value);
            }, array_fill(0, count($row), 'GBK'), array_fill(0, count($row), 'UTF-8'), $row);
            // 处理每一行数据
            //0 : "交易时间" 1 : "交易分类" 2 : "交易对方" 3 : "对方账号" 4 : "商品说明" 5 : "收/支" 6 : "金额" 7 : "收/付款方式" 8 : "交易状态" 9 :"交易订单号" 10 : "商家订单号" 11 : "备注"
            if (!empty($row[6]) && is_numeric($row[6]) && $row[6] > 0 && $row[5] <> '不计收支') {
                $date = $row[0];
                $remark = $row[4] . '【' . $row[2] . '】';
                $data[] = [
                    'user_id' => $user_id,
                    'type' => $row[5] !== '收入' ? 20 : 10,
                    'category_id' => 0,
                    'amount_type' => '支付宝',
                    'amount' => trim($row[6]),
                    'date' => date('Y-m-d', strtotime($date)),
                    'year' => date('Y', strtotime($date)),
                    'month' => date('m', strtotime($date)),
                    'day' => date('d', strtotime($date)),
                    'remark' => trim($remark),
                    'category_name' => $row[1] ?? '其它',
                ];
            }
        }
        fclose($file);
        return self::success_return('success', $data);
    }

    private function read_wechat_csv($filePath, $user_id, $fileKey)
    {

        $data = [];


        // 打开文件
        $file = fopen($filePath, 'r');

        if ($file === false) {
            return self::error_return('文件打开失败');
        }
        $start = false;

        while (($row = fgetcsv($file)) !== false) {

            if ($row[0] === '交易时间') {
                $start = true;
                continue;
            }
            if (!$start) {
                continue;
            }

            $srow = str_replace(["\\", "\"", "\t"], "", join(',', $row));
            $row = explode(',', $srow);

            //0 : "交易时间" 1 : "交易类型" 2 : "交易对方" 3 : "商品" 4 : "收/支" 5 : "金额(元)" 6 : "支付方式" 7 : "当前状态"
            // 8 : "交易单号" 9 : "商户单号" 10 : "备注"

            $row[5] = str_replace("¥", "", $row[5]);
            $date = $row[0];
            $tmp = [
                'user_id' => $user_id,
                'type' => $row[4] == '支出' ? 20 : 10,
                'category_id' => 0,
                'amount_type' => '微信',
                'amount' => $row[5],
                'date' => date('Y-m-d', strtotime($date)),
                'year' => date('Y', strtotime($date)),
                'month' => date('m', strtotime($date)),
                'day' => date('d', strtotime($date)),
                'remark' => $row[1] . '【' . $row[2] . '】' . '(' . $row[3] . ')',
                'category_name' => '微信同步',
            ];

            $data[] = $tmp;
        }
        fclose($file);
        return self::success_return('success', $data);
    }

    //导入
    public function import_file($user_id, $args = [])
    {
        $source_type = $args['source_type'] ?? 'bs_custom';//账单来源，默认为自定义账单，alipay，wechat
        $file = request()->file('file');
        if (!$file) {
            return $this->error_return('请选择文件');
        }
        if (!$file->isValid()) {
            return $this->error_return('文件上传失败');
        }
        $ext = strtolower($file->extension());
        $config = config('upload');
        if (!in_array($ext, ['xls', 'xlsx', 'csv'])) {
            return $this->error_return('文件类型不允许', $ext);
        }
        if ($file->getSize() > $config['allow_size']) {
            return $this->error_return('文件大小超出限制');
        }
        $fileKey = $file->md5() . 'xx';
        $filePath = $file->getRealPath();

        if ($ext == 'csv') {
            if (!$data = cache($fileKey)) {
                if ($source_type == 'alipay') {
                    //读取csv文件
                    $res = $this->read_alipay_csv($filePath, $user_id, $fileKey);
                } elseif ($source_type == 'wechat') {
                    $res = $this->read_wechat_csv($filePath, $user_id, $fileKey);
                } else {
                    return self::error_return('自定义仅支持xls和xlsx格式的文件');
                }
                if ($res['code'] !== 0) {
                    return $res;
                }
                $data = $res['data'] ?? [];
                if ($data) {
                    cache($fileKey, $data, 3600);
                }
            }
            return self::success_return('success', [
                'file_key' => $fileKey,
                'total' => count($data),
                'list' => $data,
            ]);
        }


        $reader = IOFactory::createReader('Xlsx');
        try {
            $spreadsheet = $reader->load($filePath);
        } catch (Exception $e) {
            return $this->error_return('文件读取失败');
        }
        $sheet = $spreadsheet->getActiveSheet();
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
        $data = [];
        $modelCategory = new Categories();
        for ($row = 2; $row <= $highestRow; $row++) {
            $rowData = [];
            for ($col = 1; $col <= $highestColumnIndex; $col++) {
                $rowData[] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
            }
            if (empty($rowData[0])) {
                return $this->error_return('第' . $row . '行，收支方式不能为空');
            }
            if (empty($rowData[1])) {
                return $this->error_return('第' . $row . '行，分类名称不能为空');
            }
            if (empty($rowData[3])) {
                return $this->error_return('第' . $row . '行，金额不能为空');
            }
            if (empty($rowData[4])) {
                return $this->error_return('第' . $row . '行，日期不能为空');
            }
            $date = trim($rowData[4]);
            if (strpos($date, '-') === false) {
                return $this->error_return('第' . $row . '行，日期格式不正确');
            }

            $category_id = $modelCategory->get_value(
                [['user_id', '=', $user_id], ['name', '=', $rowData[1]]], 'id');
            if (!$category_id) {
                $category_id = (new Categories())->data_add([
                    'user_id' => $user_id,
                    'name' => $rowData[1],
                    'type' => $rowData[0] == '收入' ? 10 : 20,
                    'icon' => 'red-packet'
                ]);
            }
            $data[] = [
                'user_id' => $user_id,
                'type' => $rowData[0] == '收入' ? 10 : 20,
                'category_id' => $category_id,
                'amount_type' => trim($rowData[2] ?? ''),
                'amount' => bcmul($rowData[3], 1, 2),
                'date' => $date,
                'year' => date('Y', strtotime($date)),
                'month' => date('m', strtotime($date)),
                'day' => date('d', strtotime($date)),
                'remark' => trim($rowData[5]),
                'category_name' => $rowData[1],
            ];
        }
        if (empty($data)) {
            return $this->error_return('未识别到数据，请按模板格式填写');
        }


        cache($fileKey, $data, 3600);

        return self::success_return('success', [
            'total' => count($data),
            'file_key' => $fileKey,
            'list' => $data
        ]);
    }

    //周期账期执行
    public function cycle_bill_execute()
    {
        $model = new CashflowsCycle();
        $where = [
            ['next_time', '<=', date('Y-m-d H:i:s')]
        ];
        $data = $model->data_list($where, '*', 'id asc', 1, 50);
        if (!$data) {
            return self::success_return('暂无需要执行的周期账单');
        }
        $modelCashflows = new Cashflows();
        foreach ($data as $item) {
            $log = $modelCashflows->data_get([['id', '=', $item['cashflows_id']]]);
            if (!$log) {
                (new CashflowsCycle())->data_del([['id', '=', $item['id']]]);//删除无效的
            }
            Db::startTrans();
            try {
                $log['date'] = date('Y-m-d');
                $log['year'] = date('Y');
                $log['month'] = date('m');
                $log['day'] = date('d');
                unset($log['id'], $log['created_at'], $log['updated_at']);
                $id = (new Cashflows())->data_add($log);
                if (!$id) {
                    throw new \Exception('添加周期账单失败');
                }
                $nextTime = self::getNextTime($item['cycle_type'], $log['date'], $item['cycle_days']);
                $res = (new CashflowsCycle())->data_update([['id', '=', $item['id']]], [
                    'next_time' => $nextTime
                ]);
                if (!$res) {
                    throw new \Exception('更新周期账单失败');
                }
                bs_queue(self::class, 'sync_count', ['user_id' => $log['user_id']], time(), 2);
                if (!empty($log['budget_id'])) {
                    bs_queue(BudgetResult::class, 'update_amount', [
                        'user_id' => $log['user_id'],
                        'budget_id' => $log['budget_id']
                    ], time(), 2);
                }
            } catch (\Exception $e) {
                Db::rollback();
            }
            Db::commit();
        }

        return self::success_return('执行完成');
    }
}
